<?php
class Database {
    private $host = 'sql310.infinityfree.com';
    private $db_name = 'if0_37529224_535g';
    private $username = 'if0_37529224';
    private $password = 'TNtpETTqBQSJj';
    private $conn;

    public function getConnection() {
        $this->conn = null;
        
        try {
            $this->conn = new PDO(
                "mysql:host=" . $this->host . ";port=3306;dbname=" . $this->db_name . ";charset=utf8mb4",
                $this->username,
                $this->password,
                [
                    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
                    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
                    PDO::ATTR_EMULATE_PREPARES => false,
                    PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8mb4"
                ]
            );
        } catch(PDOException $exception) {
            // Log error but don't display it to users
            error_log("Database connection error: " . $exception->getMessage());
            return null;
        }
        
        return $this->conn;
    }
}

// Create database connection
$database = new Database();
$pdo = $database->getConnection();

// Check if tables exist and create missing ones if needed
try {
    // Check if users table has required columns
    $stmt = $pdo->query("DESCRIBE users");
    $columns = $stmt->fetchAll(PDO::FETCH_COLUMN);
    
    // Add missing columns to users table
    if (!in_array('first_name', $columns)) {
        $pdo->exec("ALTER TABLE users ADD COLUMN first_name VARCHAR(50) NOT NULL DEFAULT ''");
    }
    if (!in_array('last_name', $columns)) {
        $pdo->exec("ALTER TABLE users ADD COLUMN last_name VARCHAR(50) NOT NULL DEFAULT ''");
    }
    if (!in_array('phone', $columns)) {
        $pdo->exec("ALTER TABLE users ADD COLUMN phone VARCHAR(20) DEFAULT NULL");
    }
    if (!in_array('role', $columns)) {
        $pdo->exec("ALTER TABLE users ADD COLUMN role ENUM('customer', 'admin') DEFAULT 'customer'");
    }
    if (!in_array('email_verified', $columns)) {
        $pdo->exec("ALTER TABLE users ADD COLUMN email_verified BOOLEAN DEFAULT FALSE");
    }
    if (!in_array('remember_token', $columns)) {
        $pdo->exec("ALTER TABLE users ADD COLUMN remember_token VARCHAR(255) DEFAULT NULL");
    }
    if (!in_array('created_at', $columns)) {
        $pdo->exec("ALTER TABLE users ADD COLUMN created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP");
    }
    if (!in_array('updated_at', $columns)) {
        $pdo->exec("ALTER TABLE users ADD COLUMN updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP");
    }

    // Check if products table has required columns
    $stmt = $pdo->query("DESCRIBE products");
    $columns = $stmt->fetchAll(PDO::FETCH_COLUMN);
    
    if (!in_array('description', $columns)) {
        $pdo->exec("ALTER TABLE products ADD COLUMN description TEXT");
    }
    if (!in_array('image_url', $columns)) {
        $pdo->exec("ALTER TABLE products ADD COLUMN image_url VARCHAR(255) DEFAULT NULL");
    }
    if (!in_array('stock_quantity', $columns)) {
        $pdo->exec("ALTER TABLE products ADD COLUMN stock_quantity INT DEFAULT 0");
    }
    if (!in_array('is_featured', $columns)) {
        $pdo->exec("ALTER TABLE products ADD COLUMN is_featured BOOLEAN DEFAULT FALSE");
    }
    if (!in_array('is_active', $columns)) {
        $pdo->exec("ALTER TABLE products ADD COLUMN is_active BOOLEAN DEFAULT TRUE");
    }
    if (!in_array('created_at', $columns)) {
        $pdo->exec("ALTER TABLE products ADD COLUMN created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP");
    }
    if (!in_array('updated_at', $columns)) {
        $pdo->exec("ALTER TABLE products ADD COLUMN updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP");
    }

    // Check if orders table has required columns
    $stmt = $pdo->query("DESCRIBE orders");
    $columns = $stmt->fetchAll(PDO::FETCH_COLUMN);
    
    if (!in_array('order_number', $columns)) {
        $pdo->exec("ALTER TABLE orders ADD COLUMN order_number VARCHAR(50) UNIQUE NOT NULL DEFAULT ''");
    }
    if (!in_array('total_amount', $columns)) {
        $pdo->exec("ALTER TABLE orders ADD COLUMN total_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00");
    }
    if (!in_array('status', $columns)) {
        $pdo->exec("ALTER TABLE orders ADD COLUMN status ENUM('pending', 'processing', 'shipped', 'delivered', 'cancelled') DEFAULT 'pending'");
    }
    if (!in_array('shipping_address', $columns)) {
        $pdo->exec("ALTER TABLE orders ADD COLUMN shipping_address TEXT");
    }
    if (!in_array('billing_address', $columns)) {
        $pdo->exec("ALTER TABLE orders ADD COLUMN billing_address TEXT");
    }
    if (!in_array('payment_method', $columns)) {
        $pdo->exec("ALTER TABLE orders ADD COLUMN payment_method VARCHAR(50)");
    }
    if (!in_array('notes', $columns)) {
        $pdo->exec("ALTER TABLE orders ADD COLUMN notes TEXT");
    }
    if (!in_array('created_at', $columns)) {
        $pdo->exec("ALTER TABLE orders ADD COLUMN created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP");
    }
    if (!in_array('updated_at', $columns)) {
        $pdo->exec("ALTER TABLE orders ADD COLUMN updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP");
    }

    // Check if order_items table has required columns
    $stmt = $pdo->query("DESCRIBE order_items");
    $columns = $stmt->fetchAll(PDO::FETCH_COLUMN);
    
    if (!in_array('created_at', $columns)) {
        $pdo->exec("ALTER TABLE order_items ADD COLUMN created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP");
    }

    // Create cart_items table if it doesn't exist
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS cart_items (
            id INT AUTO_INCREMENT PRIMARY KEY,
            user_id INT,
            product_id INT,
            quantity INT NOT NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
            FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
        )
    ");

    // Create categories table if it doesn't exist
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS categories (
            id INT AUTO_INCREMENT PRIMARY KEY,
            name VARCHAR(100) NOT NULL,
            description TEXT,
            image_url VARCHAR(255),
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
    ");

    // Create contact messages table if it doesn't exist
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS contact_messages (
            id INT AUTO_INCREMENT PRIMARY KEY,
            name VARCHAR(150) NOT NULL,
            email VARCHAR(150) NOT NULL,
            subject VARCHAR(100) NOT NULL,
            message TEXT NOT NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
    ");

    // Create newsletter subscriptions table if it doesn't exist
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS newsletter_subscriptions (
            id INT AUTO_INCREMENT PRIMARY KEY,
            email VARCHAR(150) NOT NULL UNIQUE,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
    ");

    // Insert sample data if tables are empty
    $stmt = $pdo->query("SELECT COUNT(*) FROM products");
    $product_count = $stmt->fetchColumn();
    
    if ($product_count == 0) {
        // Insert sample categories
        $pdo->exec("
            INSERT INTO categories (id, name, description, image_url) VALUES
            (1, 'Candles', 'Hand-poured artisanal candles', 'candles.jpg'),
            (2, 'Diffusers', 'Reed diffusers and room sprays', 'diffusers.jpg'),
            (3, 'Essential Oils', 'Pure essential oil blends', 'oils.jpg'),
            (4, 'Gift Sets', 'Curated fragrance collections', 'gifts.jpg')
        ");

        // Insert sample products
        $pdo->exec("
            INSERT INTO products (id, name, description, price, image_url, stock_quantity, is_featured, is_active) VALUES
            (1, 'Serenity Candle', 'A calming blend of lavender, chamomile, and vanilla. Hand-poured with soy wax and cotton wick.', 45.00, 'serenity-candle.jpg', 50, TRUE, TRUE),
            (2, 'Velvet Oud Diffuser', 'Luxurious oud wood with hints of rose and amber. Long-lasting reed diffuser.', 65.00, 'velvet-oud.jpg', 30, TRUE, TRUE),
            (3, 'Monthly Aura Box', 'Curated monthly subscription with 3-4 exclusive fragrances and lifestyle items.', 120.00, 'aura-box.jpg', 100, TRUE, TRUE),
            (4, 'Ocean Breeze Candle', 'Fresh marine notes with sea salt and eucalyptus. Perfect for relaxation.', 42.00, 'ocean-breeze.jpg', 40, FALSE, TRUE),
            (5, 'Sandalwood Essential Oil', 'Pure sandalwood essential oil. 10ml bottle with dropper.', 28.00, 'sandalwood-oil.jpg', 60, FALSE, TRUE),
            (6, 'Luxury Gift Set', 'Premium collection including candle, diffuser, and essential oil sampler.', 150.00, 'luxury-gift-set.jpg', 25, TRUE, TRUE)
        ");
    }

    // Check if admin user exists
    $stmt = $pdo->query("SELECT COUNT(*) FROM users WHERE role = 'admin'");
    $admin_count = $stmt->fetchColumn();
    
    if ($admin_count == 0) {
        // Create admin user
        $admin_password = password_hash('admin123', PASSWORD_DEFAULT);
        $pdo->exec("
            INSERT INTO users (first_name, last_name, email, password, role, email_verified) VALUES
            ('Admin', 'User', 'admin@roielune.com', '$admin_password', 'admin', TRUE)
        ");
    }

} catch(PDOException $e) {
    echo "Error setting up database: " . $e->getMessage();
}
?>
